﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

namespace TIS.DAO
{
    public class CategoryDAO
    {
        private static readonly String Url = ConfigurationManager.AppSettings["Main.ConnectionString"];
        public CategoryDAO()
        {

        }

        private SqlConnection _connection;
        private SqlConnection GetConnection()
        {

            _connection = new SqlConnection(Url);
            if (_connection.State == ConnectionState.Closed)
            {
                _connection.Open();
            }

            return _connection;
        }

        public DataTable GetImgSanPham()
        {
            try
            {
                _connection = GetConnection();
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "SELECT CategoryImage FROM Category WHERE CategoryStatus = 1 AND CategoryId = 1");
                return ds.Tables[0];
            }
            finally
            {
                _connection.Close();
            }
        }

        public DataTable GetImgDichVu()
        {
            try
            {
                _connection = GetConnection();
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "SELECT CategoryImage FROM Category WHERE CategoryStatus = 1 AND CategoryId = 2");
                return ds.Tables[0];
            }
            finally
            {
                _connection.Close();
            }
        }

        public DataTable GetImgGiaiPhap()
        {
            try
            {
                _connection = GetConnection();
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "SELECT CategoryImage FROM Category WHERE CategoryStatus = 1 AND CategoryId = 3");
                return ds.Tables[0];
            }
            finally
            {
                _connection.Close();
            }
        }


        public DataTable GetMenu(int languageId)
        {
            try
            {
                _connection = GetConnection();

                SqlParameter[] spr = new SqlParameter[1];
                spr[0] = new SqlParameter("@LanguageId", languageId);
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "SELECT * FROM Category WHERE CategoryStatus = 1 AND CategoryDisplayCenter = 1 AND LanguageId = @LanguageId ORDER BY CategoryPosition", spr);
                return ds.Tables[0];
            }
            finally
            {
                _connection.Close();
            }
        }

        public DataTable GetMenus(int languageId)
        {
            try
            {
                _connection = GetConnection();

                SqlParameter[] spr = new SqlParameter[1];
                spr[0] = new SqlParameter("@LanguageId", languageId);
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "SELECT * FROM Category WHERE CategoryStatus = 1 AND CategoryDisplayCenter = 1 AND CategoryParent = 1 AND LanguageId = @LanguageId ORDER BY CategoryPosition", spr);
                return ds.Tables[0];
            }
            finally
            {
                _connection.Close();
            }
        }

        public DataTable GetCategory(int id, int langId)
        {
            try
            {
                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[2];
                spr[0] = new SqlParameter("@id", id);
                spr[1] = new SqlParameter("@LanguageId", langId);
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "SELECT TOP 1 * FROM Category WHERE CategoryId = @id AND LanguageId = @LanguageId", spr);
                return ds.Tables[0];
            }
            finally
            {
                _connection.Close();
            }
        }

        public DataTable ListCategory(int langId)
        {
            try
            {
                _connection = GetConnection();
                var spr = new SqlParameter[1];
                spr[0] = new SqlParameter("@LanguageId", langId);
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "SELECT * FROM Category WHERE LanguageId = @LanguageId", spr);
                return ds.Tables[0];
            }
            finally
            {
                _connection.Close();
            }
        }

        public void DeleteCategory(int catId)
        {
            try
            {
                _connection = GetConnection();
                var spr = new SqlParameter[1];
                spr[0] = new SqlParameter("@catId", catId);
                SqlHelper.ExecuteNonQuery(_connection, CommandType.Text, "DELETE FROM Category WHERE CategoryId = @catId", spr);
            }
            finally
            {
                _connection.Close();
            }
        }

        public DataTable GetCategoryById(int id)
        {
            try
            {
                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[1];
                spr[0] = new SqlParameter("@CategoryId", id);
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "select * from [Category] WHERE CategoryId = @CategoryId", spr);
                return ds.Tables[0];
            }
            finally
            {
                _connection.Close();
            }
        }

        public void InsertCategory(string categoryName, int categoryParent, string categoryTitle, string categoryDescription, string categoryKeyword, string categorySeoUrl, int categoryDisplaycenter,
            int categoryPosition, int langId, int categoryStatus, string categoryImage)
        {

            try
            {
                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[11];
                spr[0] = new SqlParameter("@CategoryName", categoryName);
                spr[1] = new SqlParameter("@CategoryParent", categoryParent);
                spr[2] = new SqlParameter("@CategoryTitle", categoryTitle);
                spr[3] = new SqlParameter("@CategoryDescription", categoryDescription);
                spr[4] = new SqlParameter("@CategoryKeyword", categoryKeyword);
                spr[5] = new SqlParameter("@CategorySEOUrl", categorySeoUrl);
                spr[6] = new SqlParameter("@CategoryDisplayCenter", categoryDisplaycenter);
                spr[7] = new SqlParameter("@CategoryPosition", categoryPosition);
                spr[8] = new SqlParameter("@LanguageId", langId);
                spr[9] = new SqlParameter("@CategoryStatus", categoryStatus);
                spr[10] = new SqlParameter("@CategoryImage", categoryImage);

                SqlHelper.ExecuteNonQuery(_connection, CommandType.Text,
                    "INSERT Category (CategoryName, CategoryParent, CategoryTitle, CategoryDescription, CategoryKeyword, CategorySEOUrl, CategoryDisplayCenter, CategoryPosition, LanguageId, CategoryStatus, CategoryImage) " +
                         "VALUES(@CategoryName, @CategoryParent, @CategoryTitle, @CategoryDescription, @CategoryKeyword, @CategorySEOUrl, @CategoryDisplayCenter, @CategoryPosition, @LanguageId, @CategoryStatus, @CategoryImage)", spr);
            }
            finally { _connection.Close(); }
        }

        public void UpdateCategory(string categoryName, int categoryParent, string categoryTitle, string categoryDescription, string categoryKeyword, string categorySeoUrl,
            int categoryDisplaycenter, int categoryPosition, int langId, int categoryStatus, string categoryImage, int categoryId)
        {
            try
            {
                if (categoryImage != "")
                {
                    _connection = GetConnection();
                    SqlParameter[] spr = new SqlParameter[12];
                    spr[0] = new SqlParameter("@CategoryName", categoryName);
                    spr[1] = new SqlParameter("@CategoryParent", categoryParent);
                    spr[2] = new SqlParameter("@CategoryTitle", categoryTitle);
                    spr[3] = new SqlParameter("@CategoryDescription", categoryDescription);
                    spr[4] = new SqlParameter("@CategoryKeyword", categoryKeyword);
                    spr[5] = new SqlParameter("@CategorySEOUrl", categorySeoUrl);
                    spr[6] = new SqlParameter("@CategoryDisplayCenter", categoryDisplaycenter);
                    spr[7] = new SqlParameter("@CategoryPosition", categoryPosition);
                    spr[8] = new SqlParameter("@LanguageId", langId);
                    spr[9] = new SqlParameter("@CategoryStatus", categoryStatus);
                    spr[10] = new SqlParameter("@CategoryImage", categoryImage);
                    spr[11] = new SqlParameter("@CategoryId", categoryId);
                    SqlHelper.ExecuteNonQuery(_connection, CommandType.Text,
                        "UPDATE Category SET CategoryName = @CategoryName, CategoryParent=@CategoryParent,CategoryTitle = @CategoryTitle,CategoryDescription = @CategoryDescription,CategoryKeyword = @CategoryKeyword," +
                        "CategorySEOUrl = @CategorySEOUrl,CategoryDisplayCenter = @CategoryDisplayCenter,CategoryPosition = @CategoryPosition," +
                        "LanguageId = @LanguageId,CategoryStatus = @CategoryStatus,CategoryImage = @CategoryImage WHERE CategoryId = @CategoryId", spr);
                }
                else
                {
                    _connection = GetConnection();
                    SqlParameter[] spr = new SqlParameter[11];
                    spr[0] = new SqlParameter("@CategoryName", categoryName);
                    spr[1] = new SqlParameter("@CategoryParent", categoryParent);
                    spr[2] = new SqlParameter("@CategoryTitle", categoryTitle);
                    spr[3] = new SqlParameter("@CategoryDescription", categoryDescription);
                    spr[4] = new SqlParameter("@CategoryKeyword", categoryKeyword);
                    spr[5] = new SqlParameter("@CategorySEOUrl", categorySeoUrl);
                    spr[6] = new SqlParameter("@CategoryDisplayCenter", categoryDisplaycenter);
                    spr[7] = new SqlParameter("@CategoryPosition", categoryPosition);
                    spr[8] = new SqlParameter("@LanguageId", langId);
                    spr[9] = new SqlParameter("@CategoryStatus", categoryStatus);
                    spr[10] = new SqlParameter("@CategoryId", categoryId);
                    SqlHelper.ExecuteNonQuery(_connection, CommandType.Text,
                        "UPDATE Category SET CategoryName = @CategoryName,CategoryParent=@CategoryParent,CategoryTitle = @CategoryTitle,CategoryDescription = @CategoryDescription,CategoryKeyword = @CategoryKeyword," +
                        "CategorySEOUrl = @CategorySEOUrl,CategoryDisplayCenter = @CategoryDisplayCenter,CategoryPosition = @CategoryPosition," +
                        "LanguageId = @LanguageId,CategoryStatus = @CategoryStatus WHERE CategoryId = @CategoryId", spr);
                }
            }
            finally { _connection.Close(); }
        }
    }
}